package com.purchasing.system.repository;

import com.purchasing.system.model.PurchaseRequest;
import com.purchasing.system.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface PurchaseRequestRepository extends JpaRepository<PurchaseRequest, Long>, JpaSpecificationExecutor<PurchaseRequest> {
    Page<PurchaseRequest> findByRequestUser(User user, Pageable pageable);
    
    Page<PurchaseRequest> findByRequestUserAndStatus(User user, PurchaseRequest.PurchaseStatus status, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE pr.requestUser.id = ?1 AND pr.createdAt BETWEEN ?2 AND ?3")
    Page<PurchaseRequest> findByRequestUserIdAndCreatedAtBetween(Long userId, LocalDateTime start, LocalDateTime end, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE pr.requestUser.id = ?1 AND pr.status = ?2 AND pr.createdAt BETWEEN ?3 AND ?4")
    Page<PurchaseRequest> findByRequestUserIdAndStatusAndCreatedAtBetween(Long userId, PurchaseRequest.PurchaseStatus status, LocalDateTime start, LocalDateTime end, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE pr.requestUser.id = ?1 AND LOWER(pr.itemName) LIKE LOWER(CONCAT('%', ?2, '%'))")
    Page<PurchaseRequest> findByRequestUserIdAndItemNameContaining(Long userId, String itemName, Pageable pageable);
    
    Page<PurchaseRequest> findByStatus(PurchaseRequest.PurchaseStatus status, Pageable pageable);
    
    Page<PurchaseRequest> findByStatusNot(PurchaseRequest.PurchaseStatus status, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE pr.createdAt BETWEEN ?1 AND ?2")
    Page<PurchaseRequest> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE LOWER(pr.requestUser.username) LIKE LOWER(CONCAT('%', ?1, '%')) OR LOWER(pr.requestUser.name) LIKE LOWER(CONCAT('%', ?1, '%'))")
    Page<PurchaseRequest> findByRequestUserNameContaining(String keyword, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE pr.status = 'PENDING' AND LOWER(pr.itemName) LIKE LOWER(CONCAT('%', ?1, '%'))")
    Page<PurchaseRequest> findPendingByItemNameContaining(String itemName, Pageable pageable);
    
    @Query("SELECT pr FROM PurchaseRequest pr WHERE pr.status = 'PENDING' AND (LOWER(pr.requestUser.name) LIKE LOWER(CONCAT('%', ?1, '%')) OR LOWER(pr.requestUser.username) LIKE LOWER(CONCAT('%', ?1, '%')))")
    Page<PurchaseRequest> findPendingByRequesterNameContaining(String requesterName, Pageable pageable);
    
    List<PurchaseRequest> findTop5ByStatusOrderByCreatedAtDesc(PurchaseRequest.PurchaseStatus status);
} 